package repository

import (
	"context"
	"database/sql"
	"errors"
	"time"

	"gitee.com/hp-huiw/family-expense-api/internal/db"
	dbmodel "gitee.com/hp-huiw/family-expense-api/internal/db/model"
	"gitee.com/hp-huiw/my-go-core/util"
	jet "github.com/go-jet/jet/v2/mysql"
	"github.com/go-jet/jet/v2/qrm"
)

type IncomeExpenseDetailType struct {
	ID                string    `db:"id"`
	AccountGroupID    string    `db:"account_group_id"`
	Name              string    `db:"name"`
	Title             string    `db:"title"`
	IncomeExpenseType string    `db:"income_expense_type"`
	Subtitle          *string   `db:"subtitle"`
	AnalysisIncluded  *bool     `db:"analysis_included"`
	DateCreated       time.Time `db:"date_created"`
	LastUpdated       time.Time `db:"last_updated"`
}

type IncomeExpenseDetailTypeBasicInfo struct {
	Name  string `db:"name"`
	Title string `db:"title"`
}

type IncomeExpenseDetailTypeRepository interface {
	Exists(ctx context.Context, accountGroupID string) (bool, error)
	ExistsByOnDiffID(ctx context.Context, accountGroupID string, incomeExpenseType *string, id *string, name *string) (bool, error)
	ExistsByOnSameID(ctx context.Context, accountGroupID string, incomeExpenseType *string, id *string, name *string) (bool, error)
	QueryMaxOrderIndex(ctx context.Context, accountGroupID string, incomeExpenseType string) (int, error)
	QueryAnalysisNotIncludedDetailTypes(ctx context.Context, accountGroupID string, incomeExpenseType string) ([]IncomeExpenseDetailTypeBasicInfo, error)
	Get(ctx context.Context, accountGroupID string, id string) (*dbmodel.IncomeExpenseDetailType, error)
	Query(ctx context.Context, accountGroupID string, incomeExpenseType string) ([]IncomeExpenseDetailType, error)
	QueryName(ctx context.Context, id string) (string, error)
	QueryNames(ctx context.Context, accountGroupID string, incomeExpenseType string) ([]string, error)
	QueryIDs(ctx context.Context, accountGroupID string, incomeExpenseType string, names []string) ([]string, error)
	QueryAccountGroupDetailTypes(ctx context.Context, incomeExpenseType string, names []string, accountGroupID string) ([]string, error)
	Insert(ctx context.Context, model *dbmodel.IncomeExpenseDetailType) error
	ResetOrder(ctx context.Context, accountGroupID string, detailTypeIDs []string) error
	DeleteByName(ctx context.Context, accountGroupID string, incomeExpenseType string, name string) error
	DeleteByIDs(ctx context.Context, ids []string) error
	BatchInsertWithTx(ctx context.Context, tx *sql.Tx, detailTypes []dbmodel.IncomeExpenseDetailType) error
	UpdateWithTx(ctx context.Context, tx *sql.Tx, model *dbmodel.IncomeExpenseDetailType) error
	DeleteWithTx(ctx context.Context, tx *sql.Tx, accountGroupID string) error
}

func NewIncomeExpenseDetailTypeRepository(db *sql.DB) IncomeExpenseDetailTypeRepository {
	return &incomeExpenseDetailTypeRepository{db: db}
}

// Exists checks if any existing data by account group.
func (r *incomeExpenseDetailTypeRepository) Exists(ctx context.Context, accountGroupID string) (bool, error) {
	return r.existsBy(ctx, accountGroupID, nil, nil, nil, nil)
}

// ExistsByOnDiffID checks if any existing data by parameters on different id value.
func (r *incomeExpenseDetailTypeRepository) ExistsByOnDiffID(ctx context.Context, accountGroupID string, incomeExpenseType *string, id *string, name *string) (bool, error) {
	return r.existsBy(ctx, accountGroupID, incomeExpenseType, id, name, util.AnyPtr(false))
}

// ExistsByOnSameID checks if any existing data by parameters on same id value.
func (r *incomeExpenseDetailTypeRepository) ExistsByOnSameID(ctx context.Context, accountGroupID string, incomeExpenseType *string, id *string, name *string) (bool, error) {
	return r.existsBy(ctx, accountGroupID, incomeExpenseType, id, name, util.AnyPtr(true))
}

// QueryMaxOrderIndex queries the max order index.
func (r *incomeExpenseDetailTypeRepository) QueryMaxOrderIndex(ctx context.Context, accountGroupID string, incomeExpenseType string) (int, error) {
	var query struct {
		MaxOrderIndex int `db:"max_order_index"`
	}

	stmt := jet.
		SELECT(
			jet.MAX(tbDetailType.OrderIndex).AS("max_order_index"),
		).
		FROM(
			tbDetailType,
		).
		WHERE(
			tbDetailType.AccountGroupID.EQ(jet.String(accountGroupID)).
				AND(tbDetailType.IncomeExpenseType.EQ(jet.String(incomeExpenseType))),
		)

	err := stmt.QueryContext(ctx, r.db, &query)

	if err != nil {
		return -1, err
	}

	return query.MaxOrderIndex, nil
}

// QueryAnalysisNotIncludedDetailTypes queries the analysis not included detail types.
func (r *incomeExpenseDetailTypeRepository) QueryAnalysisNotIncludedDetailTypes(ctx context.Context, accountGroupID string, incomeExpenseType string) ([]IncomeExpenseDetailTypeBasicInfo, error) {
	var query []struct {
		Name  string `db:"name"`
		Title string `db:"title"`
	}

	stmt := jet.
		SELECT(
			tbDetailType.Name.AS("name"),
			tbDetailType.Title.AS("title"),
		).
		FROM(
			tbDetailType,
		).
		WHERE(
			tbDetailType.AccountGroupID.EQ(jet.String(accountGroupID)).
				AND(tbDetailType.IncomeExpenseType.EQ(jet.String(incomeExpenseType))).
				AND(tbDetailType.AnalysisIncluded.IS_FALSE()),
		).
		ORDER_BY(
			tbDetailType.OrderIndex.ASC(),
		)

	err := stmt.QueryContext(ctx, r.db, &query)

	if err != nil {
		return nil, err
	}

	result := make([]IncomeExpenseDetailTypeBasicInfo, 0, len(query))

	for _, item := range query {
		result = append(result, item)
	}

	return result, nil
}

// Get gets the detail type by account group id and id.
func (r *incomeExpenseDetailTypeRepository) Get(ctx context.Context, accountGroupID string, id string) (*dbmodel.IncomeExpenseDetailType, error) {
	var query dbmodel.IncomeExpenseDetailType

	stmt := jet.
		SELECT(
			tbDetailType.AllColumns,
		).
		FROM(
			tbDetailType,
		).
		WHERE(
			tbDetailType.AccountGroupID.EQ(jet.String(accountGroupID)).
				AND(tbDetailType.ID.EQ(jet.String(id))),
		)

	if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
		if errors.Is(err, qrm.ErrNoRows) {
			return nil, nil
		}

		return nil, err
	}

	return &query, nil
}

// Query queries all detail types which associated with provided account group.
func (r *incomeExpenseDetailTypeRepository) Query(ctx context.Context, accountGroupID string, incomeExpenseType string) ([]IncomeExpenseDetailType, error) {
	condition := tbDetailType.AccountGroupID.EQ(jet.String(accountGroupID))

	if !util.IsEmptyString(incomeExpenseType) {
		condition = condition.AND(tbDetailType.IncomeExpenseType.EQ(jet.String(incomeExpenseType)))
	}

	var query []struct {
		ID                string    `db:"id"`
		AccountGroupID    string    `db:"account_group_id"`
		Name              string    `db:"name"`
		Title             string    `db:"title"`
		IncomeExpenseType string    `db:"income_expense_type"`
		Subtitle          *string   `db:"subtitle"`
		AnalysisIncluded  *bool     `db:"analysis_included"`
		DateCreated       time.Time `db:"date_created"`
		LastUpdated       time.Time `db:"last_updated"`
	}

	stmt := jet.
		SELECT(
			tbDetailType.ID.AS("id"),
			tbDetailType.IncomeExpenseType.AS("income_expense_type"),
			tbDetailType.Name.AS("name"),
			tbDetailType.Title.AS("title"),
			tbDetailType.Subtitle.AS("subtitle"),
			tbDetailType.AnalysisIncluded.AS("analysis_included"),
			tbDetailType.DateCreated.AS("date_created"),
			tbDetailType.LastUpdated.AS("last_updated"),
		).
		FROM(
			tbDetailType,
		).
		WHERE(condition).
		ORDER_BY(
			tbDetailType.OrderIndex.ASC(),
		)

	if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
		return nil, err
	}

	result := make([]IncomeExpenseDetailType, 0, len(query))

	for _, item := range query {
		result = append(result, item)
	}

	return result, nil
}

// QueryName queries the detail type name by id.
func (r *incomeExpenseDetailTypeRepository) QueryName(ctx context.Context, id string) (string, error) {
	var query struct {
		Name string `db:"name"`
	}

	stmt := jet.
		SELECT(
			tbDetailType.Name.AS("name"),
		).
		FROM(
			tbDetailType,
		).
		WHERE(
			tbDetailType.ID.EQ(jet.String(id)),
		)

	err := stmt.QueryContext(ctx, r.db, &query)

	if err != nil {
		return "", err
	}

	return query.Name, nil
}

// QueryNames queries all detail type names which associated with provided account group.
func (r *incomeExpenseDetailTypeRepository) QueryNames(ctx context.Context, accountGroupID string, incomeExpenseType string) ([]string, error) {
	query := []string{}

	stmt := jet.
		SELECT(
			tbDetailType.Name,
		).
		FROM(
			tbDetailType,
		).
		WHERE(
			tbDetailType.AccountGroupID.EQ(jet.String(accountGroupID)).
				AND(tbDetailType.IncomeExpenseType.EQ(jet.String(incomeExpenseType))).
				AND(tbDetailType.AnalysisIncluded.IS_TRUE())).
		ORDER_BY(
			tbDetailType.OrderIndex.ASC(),
		)

	err := stmt.QueryContext(ctx, r.db, &query)

	return query, err
}

// QueryIDs queries the input name list and makes sure they belong to the specific account group.
func (r *incomeExpenseDetailTypeRepository) QueryIDs(ctx context.Context, accountGroupID string, incomeExpenseType string, names []string) ([]string, error) {
	ids := []string{}

	if len(names) > 0 {
		namesExpress := db.SliceExpression(names)

		stmt := jet.
			SELECT(
				tbDetailType.ID,
			).
			FROM(
				tbDetailType,
			).
			WHERE(
				tbDetailType.AccountGroupID.EQ(jet.String(accountGroupID)).
					AND(tbDetailType.IncomeExpenseType.EQ(jet.String(incomeExpenseType))).
					AND(tbDetailType.Name.IN(namesExpress...)),
			)

		if err := stmt.QueryContext(ctx, r.db, &ids); err != nil {
			return nil, err
		}
	}

	return ids, nil
}

// QueryAccountGroupDetailTypes queries the input name list and makes sure they belong to the specific account group.
func (r *incomeExpenseDetailTypeRepository) QueryAccountGroupDetailTypes(ctx context.Context, incomeExpenseType string, names []string, accountGroupID string) ([]string, error) {
	query := []string{}

	if len(names) > 0 {
		namesExpression := db.SliceExpression(names)

		stmt := jet.
			SELECT(
				tbDetailType.Name,
			).
			FROM(
				tbDetailType,
			).
			WHERE(
				tbDetailType.AccountGroupID.EQ(jet.String(accountGroupID)).
					AND(tbDetailType.IncomeExpenseType.EQ(jet.String(incomeExpenseType))).
					AND(tbDetailType.Name.IN(namesExpression...)),
			)

		err := stmt.QueryContext(ctx, r.db, &query)

		return query, err
	}

	return query, nil
}

// Insert inserts new detail type.
func (r *incomeExpenseDetailTypeRepository) Insert(ctx context.Context, model *dbmodel.IncomeExpenseDetailType) error {
	stmt := tbDetailType.
		INSERT().
		MODEL(model)

	_, err := stmt.ExecContext(ctx, r.db)

	return err
}

// ResetOrder resets the detail types order based on the sequence of provided id list.
func (r *incomeExpenseDetailTypeRepository) ResetOrder(ctx context.Context, accountGroupID string, detailTypeIDs []string) error {
	if len(detailTypeIDs) > 0 {
		detailTypeIDsExpression := db.SliceExpression(detailTypeIDs)
		caseOperator := jet.CASE()

		for i, id := range detailTypeIDs {
			caseOperator = caseOperator.WHEN(tbDetailType.ID.EQ(jet.String(id))).THEN(jet.Int(int64(i + 1)))
		}

		stmt := tbDetailType.
			UPDATE().
			SET(
				tbDetailType.OrderIndex.SET(jet.IntExp(caseOperator)),
			).
			WHERE(
				tbDetailType.AccountGroupID.EQ(jet.String(accountGroupID)).
					AND(tbDetailType.ID.IN(detailTypeIDsExpression...)),
			)

		_, err := stmt.ExecContext(ctx, r.db)

		return err
	}

	return nil
}

// DeleteByName deletes the detail type by account group id, income/expense type and name.
func (r *incomeExpenseDetailTypeRepository) DeleteByName(ctx context.Context, accountGroupID string, incomeExpenseType string, name string) error {
	stmt := tbDetailType.
		DELETE().
		WHERE(
			tbDetailType.AccountGroupID.EQ(jet.String(accountGroupID)).
				AND(tbDetailType.IncomeExpenseType.EQ(jet.String(incomeExpenseType))).
				AND(tbDetailType.Name.EQ(jet.String(name))),
		)

	_, err := stmt.ExecContext(ctx, r.db)

	return err
}

// DeleteByIDs deletes the detail types by ids.
func (r *incomeExpenseDetailTypeRepository) DeleteByIDs(ctx context.Context, ids []string) error {
	if len(ids) > 0 {
		idsExpression := db.SliceExpression(ids)

		stmt := tbDetailType.
			DELETE().
			WHERE(
				tbDetailType.ID.IN(idsExpression...),
			)

		_, err := stmt.ExecContext(ctx, r.db)

		return err
	}

	return nil
}

func (r *incomeExpenseDetailTypeRepository) BatchDelete(ctx context.Context, incomeExpenseType string, names []string, accountGroupID string) ([]string, error) {
	ids := []string{}

	if len(names) > 0 {
		namesExpress := db.SliceExpression(names)

		stmt := tbDetailType.
			DELETE().
			WHERE(
				tbDetailType.AccountGroupID.EQ(jet.String(accountGroupID)).
					AND(tbDetailType.IncomeExpenseType.EQ(jet.String(incomeExpenseType))).
					AND(tbDetailType.Name.IN(namesExpress...)),
			)

		if _, err := stmt.ExecContext(ctx, r.db); err != nil {
			return nil, err
		}
	}

	return ids, nil
}

// BatchInsertWithTx inserts multiple detail types with transaction.
func (*incomeExpenseDetailTypeRepository) BatchInsertWithTx(ctx context.Context, tx *sql.Tx, detailTypes []dbmodel.IncomeExpenseDetailType) error {
	stmt := tbDetailType.
		INSERT().
		MODELS(detailTypes)

	_, err := stmt.ExecContext(ctx, tx)

	return err
}

// UpdateWithTx updates detail type with transaction.
func (*incomeExpenseDetailTypeRepository) UpdateWithTx(ctx context.Context, tx *sql.Tx, model *dbmodel.IncomeExpenseDetailType) error {
	stmt := tbDetailType.
		UPDATE(tbDetailType.MutableColumns).
		MODEL(model).
		WHERE(
			tbDetailType.ID.EQ(jet.String(model.ID)),
		)

	_, err := stmt.ExecContext(ctx, tx)

	return err
}

// DeleteWithTx deletes all income and expense detail types by account group id with transaction.
func (*incomeExpenseDetailTypeRepository) DeleteWithTx(ctx context.Context, tx *sql.Tx, accountGroupID string) error {
	stmt := tbDetailType.
		DELETE().
		WHERE(
			tbDetailType.AccountGroupID.EQ(jet.String(accountGroupID)),
		)

	_, err := stmt.ExecContext(ctx, tx)

	return err
}

// ExistsBy checks if any existing data by parameters.
func (r *incomeExpenseDetailTypeRepository) existsBy(ctx context.Context, accountGroupID string, incomeExpenseType *string, id *string, name *string, checkOnSameID *bool) (bool, error) {
	condition := tbDetailType.AccountGroupID.EQ(jet.String(accountGroupID))

	if incomeExpenseType != nil && !util.IsEmptyString(*incomeExpenseType) {
		condition = condition.AND(tbDetailType.IncomeExpenseType.EQ(jet.String(*incomeExpenseType)))
	}

	if name != nil && !util.IsEmptyString(*name) {
		condition = condition.AND(tbDetailType.Name.EQ(jet.String(*name)))
	}

	if id != nil && !util.IsEmptyString(*id) {
		if *checkOnSameID {
			condition = condition.AND(tbDetailType.ID.EQ(jet.String(*id)))
		} else {
			condition = condition.AND(tbDetailType.ID.NOT_EQ(jet.String(*id)))
		}
	}

	return db.Exists(ctx, r.db, tbDetailType, condition)
}

type incomeExpenseDetailTypeRepository struct {
	db *sql.DB
}
